﻿/******************************************************/
// DALWarehouse データアクセスクラス  
/******************************************************/

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace MAA.Data
{
    /// <summary>
    /// Process data access to database refer to warehouse
    /// </summary>
    public class DALWarehouse
    {
        private string conStr = ConfigurationManager.AppSettings["connectionString"];

        /// <summary>
        /// Get all rice in warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getWhRice(string lang, long userID)
        {
            DataTable tblWh = new DataTable();
            string sql;
            // String query to get rice in warehouse by userID
            if (lang.Equals("vi"))
            {
                sql = @"SELECT   WareHouse.RiceID,
                                    Rice.TypeName,
                                    WareHouse.Amount,
                                    Rice.CurrentPrice,
                                    CAST((WareHouse.Amount * Rice.CurrentPrice) as bigint) AS TotalValue,
                                    Rice.PercentPrice
                            FROM    WareHouse, Rice
                            WHERE   WareHouse.RiceID = Rice.ID
                                AND WareHouse.UserID = @userID";
            }
            else
            {
                sql = @"SELECT   WareHouse.RiceID,
                                    Rice.TypeNameJP AS TypeName,
                                    WareHouse.Amount,
                                    Rice.CurrentPrice,
                                    CAST((WareHouse.Amount * Rice.CurrentPrice) as bigint) AS TotalValue,
                                    Rice.PercentPrice
                            FROM    WareHouse, Rice
                            WHERE   WareHouse.RiceID = Rice.ID
                                AND WareHouse.UserID = @userID";
            }
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblWh);
            conn.Close();

            return tblWh;
        }

        /// <summary>
        /// Get amount of a rice type in warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <returns></returns>
        public DataTable getWhRiceAmount(long userID, int riceID)
        {
            DataTable tblWhAmount = new DataTable();

            string sql = @"SELECT   WareHouse.Amount
                            FROM    WareHouse
                            WHERE   WareHouse.UserID = @userID
                                AND WareHouse.RiceID = @riceID";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblWhAmount);
            conn.Close();

            return tblWhAmount;
        }

        /// <summary>
        /// Add a rice to warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <param name="amount"></param>
        public void insertWhRice(long userID, int riceID, long amount)
        {
            // String query to insert a rice type into warehouse
            string sql = @"INSERT INTO  Warehouse(userID, riceID, amount)
                                VALUES (@userID, @riceID, @amount)";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            cmd.Parameters.Add("@riceID", SqlDbType.BigInt).Value = riceID;
            cmd.Parameters.Add("@amount", SqlDbType.BigInt).Value = amount;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// Reduce amount of a rice in warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <param name="amount"></param>
        public void reduceWhRice(long userID, int riceID, long amount)
        {
            SqlDataAdapter whRiceAdapter = new SqlDataAdapter();
            whRiceAdapter.UpdateCommand = new SqlCommand();

            whRiceAdapter.UpdateCommand.CommandText = @"
                UPDATE		WareHouse
                SET     	WareHouse.Amount = Amount - @amount
                WHERE   	WareHouse.UserID = @userID
	                AND 	WareHouse.RiceID = @riceID";

            whRiceAdapter.UpdateCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            whRiceAdapter.UpdateCommand.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            whRiceAdapter.UpdateCommand.Parameters.Add("@amount", SqlDbType.BigInt).Value = amount;

            whRiceAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            whRiceAdapter.UpdateCommand.Connection.Open();
            whRiceAdapter.UpdateCommand.ExecuteNonQuery();
            whRiceAdapter.UpdateCommand.Connection.Close();
        }

        /// <summary>
        /// Raise amount of a rice in warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <param name="amount"></param>
        public void raiseWhRice(long userID, int riceID, long amount)
        {
            SqlDataAdapter whRiceAdapter = new SqlDataAdapter();
            whRiceAdapter.UpdateCommand = new SqlCommand();

            whRiceAdapter.UpdateCommand.CommandText = @"
                UPDATE		WareHouse
                SET     	WareHouse.Amount = Amount + @amount
                WHERE   	WareHouse.UserID = @userID
	                AND 	WareHouse.RiceID = @riceID";

            whRiceAdapter.UpdateCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            whRiceAdapter.UpdateCommand.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            whRiceAdapter.UpdateCommand.Parameters.Add("@amount", SqlDbType.BigInt).Value = amount;

            whRiceAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            whRiceAdapter.UpdateCommand.Connection.Open();
            whRiceAdapter.UpdateCommand.ExecuteNonQuery();
            whRiceAdapter.UpdateCommand.Connection.Close();
        }

        /// <summary>
        /// Update amount of a rice in warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <param name="amount"></param>
        public void updateWhRice(long userID, int riceID, long amount)
        {
            SqlDataAdapter whRiceAdapter = new SqlDataAdapter();
            whRiceAdapter.UpdateCommand = new SqlCommand();

            whRiceAdapter.UpdateCommand.CommandText = @"
                UPDATE		WareHouse
                SET     	WareHouse.Amount = @amount
                WHERE   	WareHouse.UserID = @userID
	                AND 	WareHouse.RiceID = @riceID";

            whRiceAdapter.UpdateCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            whRiceAdapter.UpdateCommand.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            whRiceAdapter.UpdateCommand.Parameters.Add("@amount", SqlDbType.BigInt).Value = amount;

            whRiceAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            whRiceAdapter.UpdateCommand.Connection.Open();
            whRiceAdapter.UpdateCommand.ExecuteNonQuery();
            whRiceAdapter.UpdateCommand.Connection.Close();
        }

        /// <summary>
        /// Delete a rice in warehouse of user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <returns></returns>
        public void deleteWhRice(long userID, int riceID)
        {
            // String query to delete a ricetype in warehouse
            string sql = @"DELETE FROM  WareHouse
                                WHERE   WareHouse.UserID = @userID
                                AND     WareHouse.RiceID = @riceID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}